{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "1dd4c8f4-0896-4491-9c1a-eac37432a419",
   "metadata": {},
   "source": [
    "# 数据整理，join, combine, reshape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "61abf415-3826-4b42-aaad-c065bc8a7203",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "84b2e913-9d3b-483c-9974-c755a8d4d018",
   "metadata": {
    "tags": []
   },
   "source": [
    "## 分层索引"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "71a80768-c71e-4d8b-aa41-06c75fa92845",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a  1    0.903614\n",
       "   2    0.833410\n",
       "   3    0.898359\n",
       "b  1    0.310216\n",
       "   3    0.277785\n",
       "c  1    0.253401\n",
       "   2    0.326503\n",
       "d  2    0.058624\n",
       "   3    0.036489\n",
       "dtype: float64"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.Series(np.random.uniform(size=9),\n",
    "                 index=[[\"a\", \"a\", \"a\", \"b\", \"b\", \"c\", \"c\", \"d\", \"d\"],\n",
    "                        [1, 2, 3, 1, 3, 1, 2, 2, 3]])\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "5e962356-a21c-486e-be2d-6fe6c186e7cd",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "MultiIndex([('a', 1),\n",
       "            ('a', 2),\n",
       "            ('a', 3),\n",
       "            ('b', 1),\n",
       "            ('b', 3),\n",
       "            ('c', 1),\n",
       "            ('c', 2),\n",
       "            ('d', 2),\n",
       "            ('d', 3)],\n",
       "           )"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.index"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c169ecd-3a1a-4989-b8ef-3ac8ff1a3061",
   "metadata": {},
   "source": [
    "### 部分索引"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "ade571bc-549f-4bc4-8988-f6b36e32e310",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    0.310216\n",
       "3    0.277785\n",
       "dtype: float64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[\"b\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "150f644a-3389-4869-b71d-4c59073f9a73",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "b  1    0.310216\n",
       "   3    0.277785\n",
       "c  1    0.253401\n",
       "   2    0.326503\n",
       "dtype: float64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[\"b\":\"c\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "c1cff954-8b3f-4654-ad6c-fd3518d105dd",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "b  1    0.310216\n",
       "   3    0.277785\n",
       "d  2    0.058624\n",
       "   3    0.036489\n",
       "dtype: float64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.loc[[\"b\",\"d\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "7e9a3417-5486-4b45-857e-75b3984f7983",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    0.833410\n",
       "c    0.326503\n",
       "d    0.058624\n",
       "dtype: float64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 第二等级索引\n",
    "data.loc[:, 2]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fba68138-8c2d-4256-a9ac-f166bc22a639",
   "metadata": {},
   "source": [
    "### unstack：多层索引转DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "fac9c2e5-04de-4532-8886-a6fa5230ca8c",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>0.903614</td>\n",
       "      <td>0.833410</td>\n",
       "      <td>0.898359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>0.310216</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.277785</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>0.253401</td>\n",
       "      <td>0.326503</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.058624</td>\n",
       "      <td>0.036489</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          1         2         3\n",
       "a  0.903614  0.833410  0.898359\n",
       "b  0.310216       NaN  0.277785\n",
       "c  0.253401  0.326503       NaN\n",
       "d       NaN  0.058624  0.036489"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.unstack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "cca08031-530c-468e-971a-0f926513975e",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a  1    0.903614\n",
       "   2    0.833410\n",
       "   3    0.898359\n",
       "b  1    0.310216\n",
       "   3    0.277785\n",
       "c  1    0.253401\n",
       "   2    0.326503\n",
       "d  2    0.058624\n",
       "   3    0.036489\n",
       "dtype: float64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.unstack().stack()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3547be60-9a97-4281-8b99-d5f00e566242",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 让DataFrame的每个轴都为分层索引"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "217bf544-d91e-4cab-bf29-99bfff8c4ee5",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">Ohio</th>\n",
       "      <th>Colorado</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Green</th>\n",
       "      <th>Red</th>\n",
       "      <th>Green</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">a</th>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">b</th>\n",
       "      <th>1</th>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Ohio     Colorado\n",
       "    Green Red    Green\n",
       "a 1     0   1        2\n",
       "  2     3   4        5\n",
       "b 1     6   7        8\n",
       "  2     9  10       11"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame = pd.DataFrame(np.arange(12).reshape(4,3),\n",
    "                        index=[[\"a\",\"a\",\"b\",\"b\"], [1,2,1,2]],\n",
    "                        columns=[[\"Ohio\", \"Ohio\", \"Colorado\"],\n",
    "                                    [\"Green\", \"Red\", \"Green\"]])\n",
    "frame"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "98ca3e3f-1322-4d80-a60f-539b2600b548",
   "metadata": {},
   "source": [
    "### 每层索引都可以被命名"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "1cd3c37a-8e02-4df0-bf12-9d1db84c242c",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>state</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Ohio</th>\n",
       "      <th>Colorado</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>color</th>\n",
       "      <th>Green</th>\n",
       "      <th>Red</th>\n",
       "      <th>Green</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>key1</th>\n",
       "      <th>key2</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">a</th>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">b</th>\n",
       "      <th>1</th>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "state      Ohio     Colorado\n",
       "color     Green Red    Green\n",
       "key1 key2                   \n",
       "a    1        0   1        2\n",
       "     2        3   4        5\n",
       "b    1        6   7        8\n",
       "     2        9  10       11"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame.index.names = [\"key1\", \"key2\"]\n",
    "frame.columns.names = [\"state\", \"color\"]\n",
    "frame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "4dfc3268-61e0-4e71-a9ab-2e49c0039986",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>color</th>\n",
       "      <th>Green</th>\n",
       "      <th>Red</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>key1</th>\n",
       "      <th>key2</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">a</th>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">b</th>\n",
       "      <th>1</th>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "color      Green  Red\n",
       "key1 key2            \n",
       "a    1         0    1\n",
       "     2         3    4\n",
       "b    1         6    7\n",
       "     2         9   10"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame.loc[:,\"Ohio\"] # equal frame[\"Ohio\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9ff244e0-9c16-4c45-ba3d-27eb1f2c37f6",
   "metadata": {},
   "source": [
    "## 按索引等级汇总统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "a5a50186-eca9-409d-92a9-57a26863e9a6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th>state</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Ohio</th>\n",
       "      <th>Colorado</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>color</th>\n",
       "      <th>Green</th>\n",
       "      <th>Red</th>\n",
       "      <th>Green</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>key2</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6</td>\n",
       "      <td>8</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12</td>\n",
       "      <td>14</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "state  Ohio     Colorado\n",
       "color Green Red    Green\n",
       "key2                    \n",
       "1         6   8       10\n",
       "2        12  14       16"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame.groupby(level=\"key2\").sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "1aa789e9-3fcb-4936-8d6e-5e54fedba70b",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>color</th>\n",
       "      <th>Green</th>\n",
       "      <th>Red</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>key1</th>\n",
       "      <th>key2</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">a</th>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">b</th>\n",
       "      <th>1</th>\n",
       "      <td>14</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "color      Green  Red\n",
       "key1 key2            \n",
       "a    1         2    1\n",
       "     2         8    4\n",
       "b    1        14    7\n",
       "     2        20   10"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame.groupby(level=\"color\", axis=\"columns\").sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "689ce3dc-bb62-4488-85b6-47fea5b6a68e",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
